package org.example.TOOL;

import org.example.TOOL.Start.Config;

import java.sql.*;

public class GYHCL {

    // 数据库连接信息从配置类读取
    private static final String DB_URL = Config.url;
    private static final String USER = Config.user;
    private static final String PASS = Config.password;
    private static final String DB_DRIVER = Config.driver;

    public static void main(String[] args) {
        // 表名需要替换为实际使用的表名
        String tableName = "pd78_gccch";

        try {
            // 加载JDBC驱动程序
            Class.forName(DB_DRIVER);

            // 获取数据库连接
            try (Connection conn = DriverManager.getConnection(DB_URL, USER, PASS)) {
                // 调用方法进行归一化处理
                normalizeZfField(conn, tableName);
            }
        } catch (ClassNotFoundException e) {
            System.err.println("JDBC驱动未找到！");
            e.printStackTrace();
        } catch (SQLException e) {
            System.err.println("数据库连接失败！");
            e.printStackTrace();
        }
    }

    /**
     * 对指定表格中的数据按zydm字段分组，并对zf字段进行归一化处理。
     * @param conn 数据库连接对象
     * @param table 表名
     * @throws SQLException 如果执行SQL操作时发生错误
     */
    public static void normalizeZfField(Connection conn, String table) throws SQLException {
        // SQL语句：获取所有不同的zydm值
        String selectGroups = "SELECT DISTINCT zydm FROM " + table;

        // 使用try-with-resources确保资源自动关闭
        try (Statement stmt = conn.createStatement(); ResultSet rs = stmt.executeQuery(selectGroups)) {

            // 遍历每个zydm组
            while (rs.next()) {
                String zydm = rs.getString("zydm");

                // SQL语句：获取当前zydm组内的最小和最大zf值
                String minMaxQuery = "SELECT MIN(zf) AS min_zf, MAX(zf) AS max_zf FROM " + table + " WHERE zydm = ? AND zf IS NOT NULL";
                try (PreparedStatement psMinMax = conn.prepareStatement(minMaxQuery)) {
                    psMinMax.setString(1, zydm);
                    ResultSet rsMinMax = psMinMax.executeQuery();

                    if (rsMinMax.next()) {
                        double minZf = rsMinMax.getDouble("min_zf");
                        double maxZf = rsMinMax.getDouble("max_zf");

                        // 如果最小值是负数，则保留它；否则用0作为最小值
                        double effectiveMin = minZf < 0 ? minZf : 0;
                        double range = maxZf - effectiveMin;

                        // 如果范围为0（即所有zf值相同），则避免除以0，直接设置为默认值0.0
                        if (range == 0) {
                            updateNormalizedScore(conn, table, zydm, 0.0);
                        } else {
                            // 更新归一化后的分数
                            String updateQuery = "UPDATE " + table + " SET ztzydf = (zf - ?) / ? WHERE zydm = ?";
                            try (PreparedStatement psUpdate = conn.prepareStatement(updateQuery)) {
                                psUpdate.setDouble(1, effectiveMin);
                                psUpdate.setDouble(2, range);
                                psUpdate.setString(3, zydm);
                                psUpdate.executeUpdate();
                            }
                        }
                    }
                }
            }
        }
    }

    /**
     * 更新ztzydf字段为指定的score值。
     * @param conn 数据库连接对象
     * @param table 表名
     * @param zydm 专业代码
     * @param score 分数
     * @throws SQLException 如果执行SQL操作时发生错误
     */
    private static void updateNormalizedScore(Connection conn, String table, String zydm, double score) throws SQLException {
        String sql = "UPDATE " + table + " SET ztzydf = ? WHERE zydm = ?";
        try (PreparedStatement ps = conn.prepareStatement(sql)) {
            ps.setDouble(1, score);
            ps.setString(2, zydm);
            ps.executeUpdate();
        }
    }
}